
Public Class UrineResultHistory

    Protected _year As String
    Protected _police As New Police()
    Protected _police_full_name As String
    Protected _police_age As Integer
    Protected _ph As Double
    Protected _spgr As Double
    Protected _rbc As String
    Protected _wbc As String
    Protected _epithelium_cell As String
    Protected _urine_result_status As Boolean
    Protected _urine_color As New Color()
    Protected _urine_appearance As New Appearance()
    Protected _urine_albumin As New UrineAlbumin()
    Protected _urine_sugar As New UrineSugar()
    Protected _urine_abnormal_status_history As New UrineAbnormalStatusHistory()
    Protected _Data As ArrayList
    Protected _DataCurrent As Integer

    Public Property police() As Police
        Get
            Return _police
        End Get
        Set(ByVal value As Police)
            _police = value
        End Set
    End Property

    Public Property year() As String
        Get
            Return _year
        End Get
        Set(ByVal value As String)
            _year = value
        End Set
    End Property

    Public Property police_full_name() As String
        Get
            Return _police_full_name
        End Get
        Set(ByVal value As String)
            _police_full_name = value
        End Set
    End Property

    Public Property police_age() As Integer
        Get
            Return _police_age
        End Get
        Set(ByVal value As Integer)
            _police_age = value
        End Set
    End Property

    Public Property ph() As Double
        Get
            Return _ph
        End Get
        Set(ByVal value As Double)
            _ph = value
        End Set
    End Property

    Public Property spgr() As Double
        Get
            Return _spgr
        End Get
        Set(ByVal value As Double)
            _spgr = value
        End Set
    End Property

    Public Property rbc() As String
        Get
            Return _rbc
        End Get
        Set(ByVal value As String)
            _rbc = value
        End Set
    End Property

    Public Property wbc() As String
        Get
            Return _wbc
        End Get
        Set(ByVal value As String)
            _wbc = value
        End Set
    End Property

    Public Property epithelium_cell() As String
        Get
            Return _epithelium_cell
        End Get
        Set(ByVal value As String)
            _epithelium_cell = value
        End Set
    End Property

    Public Property urine_result_status() As Boolean
        Get
            Return _urine_result_status
        End Get
        Set(ByVal value As Boolean)
            _urine_result_status = value
        End Set
    End Property

    Public Property urine_color() As Color
        Get
            Return _urine_color
        End Get
        Set(ByVal value As Color)
            _urine_color = value
        End Set
    End Property

    Public Property urine_appearance() As Appearance
        Get
            Return _urine_appearance
        End Get
        Set(ByVal value As Appearance)
            _urine_appearance = value
        End Set
    End Property

    Public Property urine_albumin() As UrineAlbumin
        Get
            Return _urine_albumin
        End Get
        Set(ByVal value As UrineAlbumin)
            _urine_albumin = value
        End Set
    End Property

    Public Property urine_sugar() As UrineSugar
        Get
            Return _urine_sugar
        End Get
        Set(ByVal value As UrineSugar)
            _urine_sugar = value
        End Set
    End Property

    Public Function getPoliceUrineInOrganizationAll(ByVal inOrganizationID As String, ByVal inYear As String) As DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_PoliceUrineResultHistory"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "PoliceUrine")

        Return objDs
    End Function

    Protected Function getsPoliceUrineInOrganizationAll(ByVal inOrganizationID As String, ByVal inYear As String) As ArrayList
        Dim outResult As New ArrayList()
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_PoliceUrineResultHistory"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "PoliceUrine")

        For Each objDr As System.Data.DataRow In objDs.Tables("PoliceUrine").Rows
            Dim obj As UrineResultHistory = New UrineResultHistory
            obj.Police.citizen_id = objDr.Item("citizen_id")
            obj.police_full_name = objDr.Item("full_name")
            obj.police_age = Utilities.ClearDBNullToMinValue(objDr.Item("age"), TypeCode.Int32)
            obj.urine_color.color_id = Utilities.ClearDBNullToMinValue(objDr.Item("color_id"), TypeCode.Int32)
            obj.urine_appearance.appearance_id = Utilities.ClearDBNullToMinValue(objDr.Item("appearance_id"), TypeCode.Int32)
            obj.ph = Utilities.ClearDBNullToMinValue(objDr.Item("ph"), TypeCode.Double)
            obj.spgr = Utilities.ClearDBNullToMinValue(objDr.Item("spgr"), TypeCode.Double)
            obj.urine_albumin.urine_albumin_id = Utilities.ClearDBNullToMinValue(objDr.Item("albumin_id"), TypeCode.Int32)
            obj.urine_sugar.urine_sugar_id = Utilities.ClearDBNullToMinValue(objDr.Item("sugar_id"), TypeCode.Int32)
            obj.rbc = Utilities.ClearDBNullToEmptyString(objDr.Item("rbc"))
            obj.wbc = Utilities.ClearDBNullToEmptyString(objDr.Item("wbc"))
            obj.epithelium_cell = Utilities.ClearDBNullToEmptyString(objDr.Item("epithelium_cell"))

            outResult.Add(obj)
        Next

        Return outResult
    End Function

    Public Function insertPoliceUrineResultHistory(ByVal inPoliceUrineResultHistory As UrineResultHistory) As Boolean
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_INSERT_PoliceUrineResultHistory"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", _
                Utilities.SendToDatabase(Utilities.ConvertCitizenID(inPoliceUrineResultHistory.police.citizen_id)))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.year))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.police.organization.organization_id))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationNameTha", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.police.organization.organization_name_tha))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inColorID", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.urine_color.color_id))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inAppearanceID", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.urine_appearance.appearance_id))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inPh", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.ph))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inSpgr", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.spgr))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inAlbuminID", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.urine_albumin.urine_albumin_id))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inSugarID", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.urine_sugar.urine_sugar_id))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inRbc", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.rbc))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inWbc", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.wbc))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inEpitheliumCell", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.epithelium_cell))
            objCmd.Parameters.Add(objParam)

            _urine_abnormal_status_history.deletePoliceUrineResultAbnormalStatus(inPoliceUrineResultHistory)
            If Not IsDBNull(Utilities.SendToDatabase(inPoliceUrineResultHistory.ph)) Then
                inPoliceUrineResultHistory.urine_result_status = _
                    _urine_abnormal_status_history.insertPoliceUrineAbnormalStatusHistory(inPoliceUrineResultHistory)
                objParam = New MySql.Data.MySqlClient.MySqlParameter("inUrineResultStatus", _
                    Utilities.SendToDatabase(inPoliceUrineResultHistory.urine_result_status))
                objCmd.Parameters.Add(objParam)
            Else
                objParam = New MySql.Data.MySqlClient.MySqlParameter("inUrineResultStatus", DBNull.Value)
                objCmd.Parameters.Add(objParam)
            End If

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inCreateUser", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.police.create_user))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inUpdateUser", _
                Utilities.SendToDatabase(inPoliceUrineResultHistory.police.update_user))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inUpdateDate", _
                Utilities.ConvertDateTimeFormat(inPoliceUrineResultHistory.police.update_date.ToString()))
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Sub LoadPoliceUrineInOrganizationAll(ByVal inOrganizationID As String, ByVal inYear As String)
        _Data = New ArrayList
        _DataCurrent = 0
        _Data = getsPoliceUrineInOrganizationAll(inOrganizationID, inYear)
    End Sub

    Public Function NextRecord() As Boolean
        If ((Not _Data Is Nothing) AndAlso (((Not _Data Is Nothing) AndAlso _
            (_DataCurrent < _Data.Count And _Data.Count > 0)))) Then
            _police.citizen_id = _Data(_DataCurrent).police.citizen_id
            _police_full_name = _Data(_DataCurrent).police_full_name
            _police_age = _Data(_DataCurrent).police_age
            _ph = _Data(_DataCurrent).ph
            _spgr = _Data(_DataCurrent).spgr
            _rbc = _Data(_DataCurrent).rbc
            _wbc = _Data(_DataCurrent).wbc
            _epithelium_cell = _Data(_DataCurrent).epithelium_cell
            _urine_color.color_id = _Data(_DataCurrent).urine_color.color_id
            _urine_appearance.appearance_id = _Data(_DataCurrent).urine_appearance.appearance_id
            _urine_albumin.urine_albumin_id = _Data(_DataCurrent).urine_albumin.urine_albumin_id
            _urine_sugar.urine_sugar_id = _Data(_DataCurrent).urine_sugar.urine_sugar_id
            _DataCurrent += 1
            Return True
        End If
        Return False
    End Function

    Public Sub insertUrineSummaryReportValue(ByVal inYear As String, ByVal inOrganizationID As String)
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_INSERT_UrineSummaryReport"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub insertUrineAbnormalSummaryReportValue(ByVal inYear As String, ByVal inOrganizationID As String)
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_INSERT_UrineAbnormalSummaryReport"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

        Catch ex As Exception
            Throw ex
        End Try
    End Sub

End Class
